/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sun.courier.dal;

import com.sun.courier.dto.Courier_CourierManageDTO;
import com.sun.courier.entity.Courier;
import com.sun.courier.entity.CourierManage;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author HONG QUAN
 */
public class CourierManageDAL {

    DataAccessHelper dah = new DataAccessHelper();

    public Boolean insertIntoCourierManage(CourierManage c) {
        int rs = 0;
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + 1 + "','" + c.getReceiver() + "','" + 1 + "','" + 1 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            rs = dah.executeUpdate(strQuery);
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (rs > 0) {
            return true;
        }
        return false;
    }

    public ArrayList<Courier_CourierManageDTO> loadCourierWilImportStore(int loginEmpId) {// Status = 2 hoac 4
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone,\n"
                + "            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "					on Courier.CourierId = CourierManage.CourierId \n"
                + "					join Employee on CourierManage.Receiver = Employee.EmpId\n"
                + "					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "					where (CourierManage.Status like '2' or CourierManage.Status like '4') and CourierManage.CurrentPosition like '1' and EmployeeLogin.EmpId = '" + loginEmpId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setStatus(2);
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadCourierInStore(int loginId) {// O day status = 6 hoac 8-- Da them vao: CourierManage.Status like '3'
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone, Receiver,\n"
                + "            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "					on Courier.CourierId = CourierManage.CourierId \n"
                + "					join Employee on CourierManage.Receiver = Employee.EmpId\n"
                + "					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "					where (CourierManage.Status like '6') and CourierManage.CurrentPosition like '1' and EmployeeLogin.EmpId='" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setStatus(rs.getInt("Status"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadCourierImportedStore(int loginId) {
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone,Receiver,\n"
                + "            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "					on Courier.CourierId = CourierManage.CourierId \n"
                + "					join Employee on CourierManage.Receiver = Employee.EmpId\n"
                + "					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "					where (CourierManage.Status like '6') and (CourierManage.CurrentPosition like '1'or CourierManage.CurrentPosition like '0') and EmployeeLogin.EmpId = '" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setStatus(2);
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadCourierExportedStore(int loginId) {//Them: xem hang da xuat tra khach hang (gop chung)
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone,\n"
                + "            Receiver, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "					on Courier.CourierId = CourierManage.CourierId \n"
                + "					join Employee on CourierManage.Sender = Employee.EmpId\n"
                + "					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "					where (CourierManage.Status like '3'or CourierManage.Status like '5') and CourierManage.CurrentPosition like '0' and EmployeeLogin.EmpId = '" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setStatus(2);
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadWillReceiveFromColleagues(int loginId) {
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone,\n"
                + "            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "					on Courier.CourierId = CourierManage.CourierId \n"
                + "					join Employee on CourierManage.Receiver = Employee.EmpId\n"
                + "					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "					where (CourierManage.Status like '3') and CourierManage.CurrentPosition like '1' and EmployeeLogin.EmpId = '" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setStatus(2);
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadWillSendColleagues(int loginId) {
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone,\n"
                + "            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "					on Courier.CourierId = CourierManage.CourierId \n"
                + "					join Employee on CourierManage.Receiver = Employee.EmpId\n"
                + "					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "					where (CourierManage.Status like '7') and CourierManage.CurrentPosition like '1' and EmployeeLogin.EmpId ='" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setStatus(7);
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadReceivedFromColleagues(int loginId) {
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone,\n"
                + "Sender, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "on Courier.CourierId = CourierManage.CourierId \n"
                + "join Employee on CourierManage.Receiver = Employee.EmpId\n"
                + "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "where (CourierManage.Status like '7') and (CourierManage.CurrentPosition like '1'or CourierManage.CurrentPosition like '0') and EmployeeLogin.EmpId = '" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setStatus(7);
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadSentToColleagues(int loginId) {//Da sua CourierManage.Status like '7'
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone,\n"
                + "Receiver, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "on Courier.CourierId = CourierManage.CourierId \n"
                + "join Employee on CourierManage.Sender = Employee.EmpId\n"
                + "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "where (CourierManage.Status like '4') and CourierManage.CurrentPosition like '0' and EmployeeLogin.EmpId = '" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setStatus(7);
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadCourierSedingStoreJTable(int loginId) {//Da sua CourierManage.Status like '7'
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone,\n"
                + "Receiver, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "on Courier.CourierId = CourierManage.CourierId \n"
                + "join Employee on CourierManage.Sender = Employee.EmpId\n"
                + "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "where (CourierManage.Status like '4') and CourierManage.CurrentPosition like '1' and EmployeeLogin.EmpId = '" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setStatus(7);
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public Boolean sendCourierToStore(CourierManage c) {
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','" + c.getReceiver() + "','" + 1 + "','" + 2 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strUpdateStatus = "Update CourierManage set CurrentPosition = '" + 0 + "' where CourierId ='" + c.getCourierId() + "' and Status='" + 1 + "'";
                int kq = dah.executeUpdate(strUpdateStatus);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public int getPositionOfCourier(CourierManage c) {
        String strQuery = "Select CurrentPosition from CourierManage where CourierId='" + c.getCourierId() + "' and Status='" + 1 + "'";
        int position = -1;
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                position = rs.getInt("CurrentPosition");
            }
        } catch (Exception e) {
        }

        return position;
    }

    public int getPositionOfDespatchingCourier(CourierManage c) {
        String strQuery = "Select CurrentPosition from CourierManage where CourierId='" + c.getCourierId() + "' and Status='" + 7 + "'";
        int position = -1;
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                position = rs.getInt("CurrentPosition");
            }
        } catch (Exception e) {
        }

        return position;
    }

    public ArrayList<CourierManage> getPositionOfSentCuorierInCurrentDay(CourierManage c) {
        String strQuery = "Select CourierId, CurrentPosition from CourierManage where Sender='" + c.getSender() + "' and Status='" + 2 + "' and UpdateTime ='" + c.getUpdatetime() + "'";
        ArrayList<CourierManage> lst = new ArrayList<CourierManage>();
        int position = -1;
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                CourierManage cm = new CourierManage();
                cm.setCurrentPosition(rs.getInt("CurrentPosition"));
                cm.setCourierId(rs.getInt("CourierId"));
                lst.add(cm);
            }
        } catch (Exception e) {
        }
        return lst;
    }

    public Boolean confirmCourierInStore(CourierManage c) {
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','"
                + c.getReceiver() + "','" + 1 + "','" + 6 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strUpdateStatus = "Update CourierManage set CurrentPosition= 0 where CourierId='" + c.getCourierId() + "'and Status='" + 2 + "'";
                int kq = dah.executeUpdate(strUpdateStatus);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public Boolean confirmCourierInDestinationStore(CourierManage c) {
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','"
                + c.getReceiver() + "','" + 1 + "','" + 8 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strUpdateStatus = "Update CourierManage set CurrentPosition= 0 where CourierId='" + c.getCourierId() + "' and Status ='" + 4 + "'";
                int kq = dah.executeUpdate(strUpdateStatus);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public Boolean exportCourierToDespatching(CourierManage c) {
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','"
                + c.getReceiver() + "','" + 1 + "','" + 3 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strPosition = "Update CourierManage set CurrentPosition = 0 where CourierId='" + c.getCourierId() + "' and Status='" + 6 + "'";
                int kq = dah.executeUpdate(strPosition);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public Boolean exportCourierToReturnCustomer(CourierManage c) {
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','"
                + c.getReceiver() + "','" + 1 + "','" + 5 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strPosition = "Update CourierManage set CurrentPosition = 0 where CourierId='" + c.getCourierId() + "' and Status='" + 8 + "'";
                int kq = dah.executeUpdate(strPosition);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public ArrayList<CourierManage> getCourierManageInfor(CourierManage c) {
        ArrayList<CourierManage> lst = new ArrayList<>();
        String strQuery = "Select * from CourierManage where CourierId ='" + c.getCourierId() + "' and Status ='" + c.getStatus() + "' and CurrentPosition ='" + c.getCurrentPosition() + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                CourierManage cm = new CourierManage();
                cm.setCourierId(rs.getInt("CourierId"));
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setStatus(rs.getInt("Status"));
                cm.setCurrentPosition(rs.getInt("CurrentPosition"));
                cm.setDescription(rs.getString("Description"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                lst.add(cm);
            }
        } catch (Exception e) {
        }

        return lst;
    }

    public Boolean despatchingConfirmReceiveFromStore(CourierManage c) {
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','"
                + c.getReceiver() + "','" + 1 + "','" + 7 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strDesStatus = "Update CourierManage set CurrentPosition='" + 0 + "' where CourierId='" + c.getCourierId() + "' and Status='" + 3 + "'";
                int kq = dah.executeUpdate(strDesStatus);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }

        return false;
    }

    public Boolean SendToDestinationStore(CourierManage c) {
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','"
                + c.getReceiver() + "','" + 1 + "','" + 4 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strDesStatus = "Update CourierManage set CurrentPosition='" + 0 + "' where CourierId='" + c.getCourierId() + "' and Status='" + 7 + "'";
                int kq = dah.executeUpdate(strDesStatus);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }

        return false;
    }

    public Boolean confirmCourierFromColleagues(CourierManage c) {//Da Sua 3 thanh 5
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','"
                + c.getReceiver() + "','" + 1 + "','" + 10 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strDesStatus = "Update CourierManage set CurrentPosition='" + 0 + "' where CourierId='" + c.getCourierId() + "' and Status='" + 5 + "'";
                int kq = dah.executeUpdate(strDesStatus);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public Boolean returnCourierToCustomer(CourierManage c) {
        String strQuery = "Insert into CourierManage values('" + c.getCourierId() + "','" + c.getSender() + "','"
                + 1 + "','" + 1 + "','" + 9 + "','" + 1 + "','" + c.getDescription() + "','" + c.getUpdatetime() + "')";
        try {
            int rs = dah.executeUpdate(strQuery);
            if (rs > 0) {
                String strDesStatus = "Update CourierManage set CurrentPosition='" + 0 + "' where CourierId='" + c.getCourierId() + "' and Status='" + 10 + "'";
                int kq = dah.executeUpdate(strDesStatus);
                if (kq > 0) {
                    return true;
                }
            }
        } catch (Exception e) {
        }

        return false;
    }

    public ArrayList<Courier_CourierManageDTO> loadExportReturnCustomerStore(int loginId) {// O day status = 6 hoac 8-- Da them vao: CourierManage.Status like '3'
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone, Receiver,\n"
                + "            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "					on Courier.CourierId = CourierManage.CourierId \n"
                + "					join Employee on CourierManage.Receiver = Employee.EmpId\n"
                + "					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "					where (CourierManage.Status like '8') and CourierManage.CurrentPosition like '1' and EmployeeLogin.EmpId='" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setStatus(rs.getInt("Status"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    public ArrayList<Courier_CourierManageDTO> loadCourierStoreExporting(int loginId) {// O day status = 6 hoac 8-- Da them vao: CourierManage.Status like '3'
        ArrayList<Courier_CourierManageDTO> lst = new ArrayList<>();
        String strQuery = "Select Courier.CourierId, CourierName, CustomerName, Courier.Phone, Receiver,\n"
                + "            Sender, CourierManage.Status, UpdateTime from Courier join CourierManage \n"
                + "					on Courier.CourierId = CourierManage.CourierId \n"
                + "					join Employee on CourierManage.Sender = Employee.EmpId\n"
                + "					join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n"
                + "					where (CourierManage.Status like '3'or CourierManage.Status like '5') and CourierManage.CurrentPosition like '1' and EmployeeLogin.EmpId='" + loginId + "'";
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while (rs.next()) {
                Courier c = new Courier();
                Courier_CourierManageDTO cmdto = new Courier_CourierManageDTO();
                c.setCourierId(rs.getInt("CourierId"));
                c.setCourierName(rs.getString("CourierName"));
                c.setCustomerName(rs.getString("CustomerName"));
                c.setPhone(rs.getString("Phone"));
                cmdto.setC(c);
                CourierManage cm = new CourierManage();
                cm.setSender(rs.getInt("Sender"));
                cm.setReceiver(rs.getInt("Receiver"));
                cm.setStatus(rs.getInt("Status"));
                cm.setUpdatetime(rs.getString("UpdateTime"));
                cmdto.setCm(cm);

                lst.add(cmdto);
            }
        } catch (Exception ex) {
            Logger.getLogger(CourierDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }
}
